package com.kaikeba.dao.imp;

import com.kaikeba.bean.User;
import com.kaikeba.bean.UserPieces;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName UserDaoMysql
 * @Description TODO
 * @Author Wuky
 * @Time 2022/1/17 16:28
 * @Version 1.0
 **/
public class UserDaoMysql implements BaseUserDao {

    //用于查询数据库中的全部用户（总数+新增）
    public static final String SQL_CONSOLE = "SELECT COUNT(userphone) data1_size,COUNT(TO_DAYS(registerTime)=TO_DAYS(NOW()) OR NULL) data1_day FROM `user`";
    //用于查询数据库中的所有用户信息
    public static final String SQL_FIND_ALL = "SELECT * FROM USER";
    //用于分页查询数据库中的用户信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM USER LIMIT ?,?";
    //通过手机号查询用户信息
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM USER WHERE USERPHONE = ?";
    //插入用户信息
    public static final String SQL_INSERT = "INSERT INTO USER VALUES(NULL,?,?,?,NOW(),NOW())";
    //修改用户信息
    public static final String SQL_UPDATE = "UPDATE USER SET NICKNAME = ?,USERPHONE = ?,`PASSWORD` = ? WHERE USERPHONE = ?";
    //删除用户信息
    public static final String SQL_DELETE = "DELETE FROM USER WHERE USERPHONE = ?";
    //更新用户登入时间
    public static final String SQL_UPDATE_LOGIN = "UPDATE USER SET LOGINTIME = NOW() WHERE USERPHONE = ?";
    //用户快件排行
    public static final String SQL_PIECES_RANKING = "select nickname,pieces from user u,(select userphone,count(1) pieces from express group by userphone) a where u.userphone = a.userphone order by pieces desc";
    //用户快件排行(前几)
    public static final String SQL_PIECES_RANKING_LIMIT = "select nickname,pieces from user u,(select userphone,count(1) pieces from express group by userphone) a where u.userphone = a.userphone order by pieces desc LIMIT ?";


    /**
     * 用于查询数据库中的全部用户
     * SELECT COUNT(userphone) data1_size,COUNT(TO_DAYS(registerTime)=TO_DAYS(NOW()) OR NULL) data1_day FROM `user`
     * @return [{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            result = state.executeQuery();
            if (result.next()){
                int data1_size = result.getInt("data1_size");
                int data1_day = result.getInt("data1_day");
                Map data1 = new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                data.add(data1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return data;
    }

    /**
     * 用于查询所有用户
     * SELECT * FROM USER
     * @param limit      是否分页的标记，true表示分页。false表示查询所有用户
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 用户的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.    填充参数(可选)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while(result.next()){
                int id = result.getInt("id");
                String nickname = result.getString("nickname");
                String userPhone = result.getString("userphone");
                String password = result.getString("password");
                Timestamp registerTime = result.getTimestamp("registerTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                User c = new User(id,nickname,userPhone,password,registerTime,loginTime);
                data.add(c);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * 根据手机号，查询用户信息
     * SELECT * FROM USER WHERE USERPHONE = ?
     * @param userPhone 手机号
     * @return 查询的用户信息，手机号不存在时返回null
     */
    @Override
    public User findByUserPhone(String userPhone) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            //3.    填充参数(可选)
            state.setString(1,userPhone);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            if(result.next()){
                int id = result.getInt("id");
                String nickname = result.getString("nickname");
                String password = result.getString("password");
                Timestamp registerTime = result.getTimestamp("registerTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                User u = new User(id,nickname,userPhone,password,registerTime,loginTime);
                return u;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 用户的录入
     * INSERT INTO USER VALUES(NULL,?,?,?,NOW(),NULL)
     * @param u 要录入的用户对象
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(User u){
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.    填充参数
            state.setString(1,u.getNickname());
            state.setString(2,u.getUserPhone());
            state.setString(3,u.getPassword());
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户的修改
     * UPDATE USER SET NICKNAME = ?,USERPHONE = ?,`PASSWORD` = ? WHERE USERPHONE = ?
     * @param userPhone  要修改的用户手机号
     * @param newUser 新的用户对象
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(String userPhone, User newUser) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newUser.getNickname());
            state.setString(2,newUser.getUserPhone());
            state.setString(3,newUser.getPassword());
            state.setString(4,userPhone);

            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }

        return false;
    }

    /**
     * 根据userPhone，删除单个用户信息
     * DELETE FROM USER WHERE USERPHONE = ?
     * @param userPhone 要删除的用户userPhone
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(String userPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;

        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1,userPhone);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }

        return false;
    }

    /**
     * 根据userPhone更新登入时间
     * UPDATE USER SET LOGINTIME = NOW() WHERE USERPHONE = ?
     * @param userPhone
     * @return
     */
    @Override
    public boolean updateLoginTime(String userPhone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE_LOGIN);
            state.setString(1,userPhone);

            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 返回所有用户快递排行
     * select nickname,pieces from user u,(select userphone,count(1) pieces from express group by userphone) awhere u.userphone = a.userphone order by pieces desc;
     * @param limit :前几项  0：返回所有
     * @return
     */
    @Override
    public List<UserPieces> userPiecesRanking(int limit) {
        ArrayList<UserPieces> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(limit!=0) {
                state = conn.prepareStatement(SQL_PIECES_RANKING_LIMIT);
                //3.    填充参数(可选)
                state.setInt(1,limit);
            }else {
                state = conn.prepareStatement(SQL_PIECES_RANKING);
            }
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while(result.next()){
                String nickname = result.getString("nickname");
                int pieces = Integer.parseInt(result.getString("pieces"));
                UserPieces userPieces = new UserPieces(nickname,pieces);
                data.add(userPieces);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }
}
